/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author SONY
 */
public class SaleOrderDAO {
    
    DataSource ds;
    
    public SaleOrderDAO() {
        try {
            Context context = new InitialContext();
            ds = (DataSource) context.lookup("java:comp/env/jdbc/FashionShopDatasource");
        } catch (Exception e) {
            System.err.println(e);
        }
    }
    
    public int insert (int MemberID, String Name, String Address, String Phone, Timestamp OrderDate, String Status, double Total, String PaymentType) {
        try {
            Connection conn = ds.getConnection();
            String sql = "INSERT INTO SaleOrder ([MemberID],[CustomerName],[ShippingAddress],[ShippingPhone],[OrderDate],[Status],[TotalPrice],[PaymentType]) VALUES(?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if(MemberID == 0) {
                pstmt.setNull(1, Types.INTEGER);
            } else {
                pstmt.setInt(1, MemberID);
            }
            pstmt.setNString(2, Name);
            pstmt.setNString(3, Address);
            pstmt.setNString(4, Phone);
            pstmt.setTimestamp(5, OrderDate);
            pstmt.setNString(6, Status);
            pstmt.setDouble(7, Total);
            pstmt.setNString(8, PaymentType);
            if (pstmt.executeUpdate() > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                rs.next();
                return rs.getInt(1); //get SaleOrderID just inserted
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return -1;
    }
    
    public ArrayList<SaleOrder> search_byMemberID(int MemberID) {
        ArrayList<SaleOrder> saleorders = new ArrayList<SaleOrder>();
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM SaleOrder WHERE MemberID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, MemberID);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                int SaleOrderID = rs.getInt("SaleOrderID");
                String CustomerName = rs.getNString("CustomerName");
                String Address = rs.getNString("ShippingAddress");
                String Phone = rs.getNString("ShippingPhone");
                Timestamp OrderDate = rs.getTimestamp("OrderDate");
                String Status = rs.getNString("Status");
                double TotalPrice = rs.getDouble("TotalPrice");             
                String PaymentType = rs.getNString("PaymentType");
                SaleOrder saleorder = new SaleOrder(SaleOrderID, MemberID, CustomerName, Address, Phone, OrderDate, Status, TotalPrice, PaymentType);
                saleorders.add(saleorder);              
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return saleorders;
    } 
    
    public ArrayList<SaleOrder> getAllSaleOrders() {
        ArrayList<SaleOrder> saleorders = new ArrayList<SaleOrder>();
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM SaleOrder";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()) {
                int SaleOrderID = rs.getInt("SaleOrderID");
                int MemberID = rs.getInt("MemberID");
                String CustomerName = rs.getNString("CustomerName");
                String Address = rs.getNString("ShippingAddress");
                String Phone = rs.getNString("ShippingPhone");
                Timestamp OrderDate = rs.getTimestamp("OrderDate");
                String Status = rs.getNString("Status");
                double TotalPrice = rs.getDouble("TotalPrice");             
                String PaymentType = rs.getNString("PaymentType");
                SaleOrder saleorder = new SaleOrder(SaleOrderID, MemberID, CustomerName, Address, Phone, OrderDate, Status, TotalPrice, PaymentType);
                saleorders.add(saleorder);
            }
        } catch(Exception e) {
            System.err.println(e);
        }
        return saleorders;
    }
    
    public ArrayList<SaleOrder> search_bySaleOrderID(int SaleOrderID) {
        ArrayList<SaleOrder> saleorders = new ArrayList<SaleOrder>();
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM SaleOrder WHERE SaleOrderID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, SaleOrderID);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                int MemberID = rs.getInt("MemberID");
                String CustomerName = rs.getNString("CustomerName");
                String Address = rs.getNString("ShippingAddress");
                String Phone = rs.getNString("ShippingPhone");
                Timestamp OrderDate = rs.getTimestamp("OrderDate");
                String Status = rs.getNString("Status");
                double TotalPrice = rs.getDouble("TotalPrice");             
                String PaymentType = rs.getNString("PaymentType");
                SaleOrder saleorder = new SaleOrder(SaleOrderID, MemberID, CustomerName, Address, Phone, OrderDate, Status, TotalPrice, PaymentType);
                saleorders.add(saleorder);              
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return saleorders;
    } 
    
    public boolean update_Status (int SaleOrderID, String Status) {
        try {
            Connection conn = ds.getConnection();
            String sql = "UPDATE SaleOrder SET Status=? WHERE SaleOrderID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setNString(1, Status);
            pstmt.setInt(2, SaleOrderID);
            if (pstmt.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return false;
    }
    
}
